package com.example.mapp.buildSql;

import com.example.ext.entity.ExtParty;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.util.Map;

/**
 * Created by youkun on 2017/9/9.
 */
public class PartyMappSql {

    public String  queryCompanyNameList(String companyName){
       return "select party_name as partyName,id  from party where party_name like '%"+companyName+"%'";

    }

    public String queryStrListCity(ExtParty extParty){
        String select ="select * from party where id = "+extParty.getId();
        return select;
    }
    public String queryList(ExtParty extParty){
        String select = " distinct p.id,p.party_name,p.party_addr,p.party_type,p.parent_id,pp.party_name as parentName,ele.ele_count,user.user_count ";
        String table ="party p left join (select id,party_name from party) pp on pp.id = p.parent_id left join sub_party_info spi on p.id=spi.id\n" +
                "        \t\t left join (select ppp.id,CONCAT(count(1)) ele_count from party ppp\n" +
                "             inner join elevator e on ppp.id = e.used_party_id or ppp.id = e.maintenance_party_id group by ppp.id) ele on ele.id=p.id\n" +
                "        \t\t left join (select ppp.id,CONCAT(count(1)) user_count from party ppp inner join party_user pu on ppp.id=pu.party_id\n" +
                "             inner join sys_user_role sur on pu.user_id=sur.user_id where sur.role_id in ('5','6','7') group by ppp.id) user on user.id = p.id";
        String whereParam="";
        if(extParty.getPartyName()!=null && extParty.getPartyName().length()>0){
            extParty.setPartyName("%"+extParty.getPartyName()+"%");
            whereParam+=" and p.party_name like #{partyName}";
        }
        if(extParty.getPartyType()!=null && extParty.getPartyType().length()>0){
            if(extParty.getPartyType().contains(",")){
                StringBuilder builder = new StringBuilder();
                String[] types = extParty.getPartyType().split(",");
                builder.append("and ( p.party_type = ").append(types[0]);
                for(int i = 1,len = types.length; i < len; i++){
                    builder.append(" or p.party_type =  ").append(types[i]);
                }
                whereParam+=builder.append(" ) ").toString();
            }else{
                whereParam+=" and p.party_type = #{partyType} ";
            }
        }
        if(extParty.getParentId()!=null && extParty.getParentId().length()>0){
            whereParam+=" and p.parent_id ="+extParty.getParentId();
        }
       if(extParty.getId() != null){
           whereParam += " and spi.parent_id = #{id} ";
       }
        String where = " 1=1 "+whereParam;
        return new SQL(){{
            SELECT(select);
            FROM(table);
            WHERE(where);
        }
        }.toString();
    }

    /*市局系统新修改的SQL*/
    public String queryCityInfoList(ExtParty extParty) {
        StringBuffer sb = new StringBuffer();

        sb.append("select py.id,party_name as partyName,party_addr as partyAddr , ");
        sb.append("party_type as partyType, ");
        sb.append("uc.userNum as userCount , ");
        sb.append("ele.eleNum as eleCount, ");
        sb.append("puc.userTotalNum as userJobNum, ");
        sb.append("ifnull(pct.contractNum,'0') as contractNumber, ");
        sb.append("ifnull(pm.pmnum,'0') as projectNum, ");
        sb.append("ifnull(so.socialNum,'0') as socialNum ");


        sb.append("from party py ");
        sb.append("left join (select pu.party_id as partyId,count(1) as userNum from sys_user su left join party_user pu ON pu.user_id = su.id ");
        sb.append("where 1=1 and ");
        sb.append(" pu.party_id='"+extParty.getId()+"' ");
        sb.append("and user_code is not null and type='user') uc ON uc.partyId = py.id ");
        sb.append("left join  (select py.id as party_id ,count(1) as eleNum from elevator.elevator el left join  party py ON (el.maintenance_party_id=py.id OR el.used_party_id = py.id) ");
        sb.append("where py.id='"+extParty.getId()+"' ) ele ON ele.party_id = py.id ");

        sb.append("LEFT JOIN (select pur.party_id as partyId,count(1) as userTotalNum from sys_user sur left join party_user pur ON pur.user_id = sur.id  ");
        sb.append("where 1=1 and ");
        sb.append(" pur.party_id='"+extParty.getId()+"' ");
        sb.append("and sur.user_code is not null) puc ON puc.partyId = py.id ");

        sb.append("left join (select p.party_id,count(1) as contractNum from  party_contract p  ");
        sb.append("where 1=1 and ");
        sb.append(" p.party_id='"+extParty.getId()+"' ) pct ON pct.party_id = py.id ");

        sb.append("left join (select pyc.party_id,COUNT(1) as pmnum from party_contract pyc left join party_contract_project pcp ON pyc.id = pcp.contract_id  ");
        sb.append(" where pyc.party_id='"+extParty.getId()+"' ) pm ON pm.party_id = py.id ");

        sb.append("left join (select paru.party_id,count(1) as socialNum from sys_user sysu left join party_user paru ON paru.user_id = sysu.id  ");
        sb.append("where 1=1 and ");
        sb.append(" paru.party_id='"+extParty.getId()+"' and sysu.user_code is not null and sysu.user_state='1' and sysu.social_list is not null ) so ON so.party_id = py.id ");

        sb.append("where py.id='"+extParty.getId()+"' ");

        if(StringUtils.isNotBlank(extParty.getPartyName())){
            sb.append("and py.party_name like '%"+extParty.getPartyName()+"%' ");
        }

        if(StringUtils.isNotBlank(extParty.getFindType())){
            sb.append("and py.party_type ='"+extParty.getFindType()+"' ");
        }
        return sb.toString();
    }

    public String queryRandomList(Map map) {
        StringBuffer sb = new StringBuffer();

        sb.append(" select distinct adp.id,adp.party_name as partyName,adp.party_addr as partyAddr,adp.party_type as partyType,adp.parent_id as parentId,adp.tousu, ");
        sb.append("  BB.chaoqi,pt.empiric_value as jifen,ele.ele_count as eleCount,pro.pro_count as proCount ,user.user_count as userCount from ( ");

        sb.append("  SELECT np.id,np.party_name,np.party_type,np.parent_id,np.party_addr,sum(np.num) AS tousu FROM( ");
        sb.append("  SELECT p.*, ee.num FROM party p LEFT JOIN( ");
        sb.append(" SELECT count(ea.id)AS num, e.used_party_id AS upid, ");
        sb.append("  e.maintenance_party_id AS mpid FROM ");
        sb.append("  elevator_appraisal ea LEFT JOIN elevator e ON e.id = ea.elevator_id ");
        sb.append("  AND ea.evaluate_type = '2' GROUP BY e.used_party_id, e.maintenance_party_id ");
        sb.append("  )AS ee ON(p.id = ee.upid OR p.id = ee.mpid) ");
        sb.append("  GROUP BY p.id, ee.num )AS np ");
        sb.append("  GROUP BY np.id, np.party_name,np.party_type,np.parent_id,np.party_addr ");
        sb.append(" ) as adp left join (select elev.used_party_id, sum( ");
        sb.append("  (case when elev.next_check_date<(DATE_FORMAT(NOW(),'%Y%m%d')) ");
        sb.append(" THEN 1 else 0 end)) as chaoqi from elevator elev group by elev.used_party_id ");
        sb.append(" ) as BB on adp.id=BB.used_party_id ");

        sb.append(" left join promotion pt on (adp.id=pt.user_id and adp.party_type=pt.type) ");

        sb.append(" left join elevator endel on ");
        sb.append(" (adp.id=endel.used_party_id or adp.id=endel.maintenance_party_id) ");
        sb.append(" left join sub_party_info spi on adp.id = spi.id ");
        sb.append(" left join (select sum(case when place_type = '重点场所' then 1 else 0 end) dianti,used_party_id party_id from elevator group by used_party_id union ");
        sb.append(" select sum(case when place_type = '重点场所' then 1 else 0 end) dianti,maintenance_party_id party_id from elevator group by maintenance_party_id ) dt on adp.id = dt.party_id "
        		+ "left join (select ppp.id,count(1) ele_count from party ppp inner join elevator e on ppp.id = e.used_party_id or ppp.id = e.maintenance_party_id) ele on ele.id=adp.id "
        		+ "left join (select ppp.id,count(SUBSTRING_INDEX(SUBSTRING_INDEX(pc.contract_Name,',',help_topic_id+1),',',-1)) pro_count from party ppp inner join party_contract pc on ppp.id=pc.first_party or ppp.id=pc.second_party " 
				+ "INNER JOIN mysql.help_topic on 1=1 "
				+ "WHERE help_topic_id < LENGTH(pc.contract_Name)-LENGTH(REPLACE(pc.contract_Name,',',''))+1 "
				+ "group by ppp.id) pro on pro.id=adp.id "
        		+ "left join (select ppp.id,count(1) user_count from party ppp inner join party_user pu on ppp.id=pu.party_id inner join sys_user_role sur on pu.user_id=sur.user_id where sur.role_id in ('5','6','7')) user on user.id = adp.id ");

        sb.append("  where 1=1 ");
        if(map.get("tousu")!=null && !map.get("tousu").equals("")){
            sb.append("  and adp.tousu>"+map.get("tousu"));
        }

        if(map.get("parentId")!=null && !map.get("parentId").equals("")){
            sb.append("  and spi.parent_id="+map.get("parentId"));
        }
        if(map.get("tilin")!=null && !map.get("tilin").equals("")){
            sb.append("  and (DATE_FORMAT(NOW(),'%Y') - SUBSTRING(endel.elevator_num,11,4)) >"+map.get("tilin"));
        }
        if(map.get("jifen")!=null && !map.get("jifen").equals("")){
            sb.append("  and pt.empiric_value<"+map.get("jifen"));
        }
        if(map.get("chaoqi")!=null && !map.get("chaoqi").equals("")){
            sb.append("  and BB.chaoqi>"+map.get("chaoqi"));
        }
        if(map.get("dianti")!=null && !map.get("dianti").equals("")){
            sb.append("  and dt.dianti>"+map.get("dianti"));
        }
        if(map.get("partyType")!=null && !map.get("partyType").equals("")){
            sb.append("  and adp.party_type = "+map.get("partyType"));
        }else{
            sb.append("  and (adp.party_type='1' or adp.party_type='2') ");
        }

        sb.append("   group by adp.id,adp.party_name,adp.party_type,adp.tousu, ");
        sb.append("  BB.chaoqi,pt.empiric_value ");

        return sb.toString();
    }

    public String queryUsePartCompanyNameList(String companyName){
        return "select party_name as partyName,id  from party where party_name like '%"+companyName+"%' and party_type = '2' ";
    }

    public String queryMaintenanceCompanyNameList(String companyName){
        return "select party_name as partyName,id  from party where party_name like '%"+companyName+"%' and party_type = '1' ";

    }

    /**
     * 机构查询SQL
     */
    public String findPartyList(ExtParty extParty) {

        StringBuffer sb = new StringBuffer();

        sb.append("select py.id,py.party_name as partyName,py.party_addr as partyAddr,py.party_type as partyType from party py where 1=1 and  py.party_type !='4' and py.party_name is not null ");

        if(StringUtils.isNotBlank(extParty.getPartyName())){
            sb.append("and py.party_name like '%"+extParty.getPartyName()+"%' ");
        }

        if(StringUtils.isNotBlank(extParty.getFindType())){
            sb.append(" and py.party_type ="+extParty.getFindType()+" ");
        }



        return sb.toString();
    }

    /**
     * 机构查询SQL
     */
    public String findAreaList(ExtParty extParty) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select py.id,py.party_name as partyName,py.party_addr as partyAddr,py.party_type as partyType from party py where py.id in ( ");
        sb.append("select DISTINCT er.maintenance_party_id as id  from  elevator er where er.branch_office in  ");
        if("2".equals(extParty.getPartyLevel())){
            sb.append(" ( select id from party where parent_id="+ extParty.getId()+") ");
        }else{
            sb.append(" ( "+extParty.getId()+") ");
        }
        sb.append(" UNION ");
        sb.append(" select DISTINCT  t.used_party_id as id  from  elevator t where t.branch_office in ");

        if("2".equals(extParty.getPartyLevel())){
            sb.append(" ( select id from party where parent_id="+ extParty.getId()+") ");
        }else{
            sb.append(" ( "+extParty.getId()+") ");
        }
        sb.append( ")" );

        if (StringUtils.isNotBlank(extParty.getPartyName())) {
            sb.append("and py.party_name like '%" + extParty.getPartyName() + "%' ");
        }

        if(StringUtils.isNotBlank(extParty.getFindType())){
            sb.append(" and py.party_type ="+extParty.getFindType()+" ");
        }

        return sb.toString();
    }


    public String queryListByLevel(Map parammap) {
        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT ");
        sb.append(" party_name as 'partyName', ");
        sb.append(" id as 'id' ");
        sb.append(" FROM party ");
        sb.append(" WHERE party_level = '3' and party_type='4' ");

        if (parammap != null && StringUtils.isNotBlank(parammap.get("partyLevel").
                toString()) && "2".equals(parammap.get("partyLevel").toString())) {
            sb.append(" and parent_id =" + parammap.get("partyId").toString());
        }
        if (parammap != null && StringUtils.isNotBlank(parammap.get("partyLevel").
                toString()) && "3".equals(parammap.get("partyLevel").toString())) {
            sb.append(" and id =" + parammap.get("partyId").toString());
        }
        return sb.toString();
    }

    public String findCensusInfo(Map parammap) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" party_id as 'partyId', ");
        sb.append(" party_name as 'partyName', ");
        sb.append(" party_addr as 'partyAddr', ");
        sb.append(" ele_count as 'eleCount', ");
        sb.append(" contract_number as 'contractNumber', ");
        sb.append(" project_num as 'projectNum', ");
        sb.append(" user_job_num as 'userJobNum', ");
        sb.append(" user_count as 'userCount', ");
        sb.append(" social_num as 'socialNum', ");
        sb.append(" party_type as 'partyType' ");
        sb.append(" FROM party_census_info where party_id =" + parammap.get("partyId") + " and create_date=" + parammap.get("credateDate"));
        return sb.toString();
    }

    /**
     * 区
     * @param extParty
     * @return
     */
    public String findCensusInfoListArea(ExtParty extParty) {
        StringBuffer sb = new StringBuffer();
        Long partyId = extParty.getId();

        sb.append(" SELECT ");
        sb.append(" party_id as 'id', ");
        sb.append(" party_name as 'partyName', ");
        sb.append(" party_addr as 'partyAddr', ");
        sb.append(" ele_count as 'eleCount', ");
        sb.append(" contract_number as 'contractNumber', ");
        sb.append(" project_num as 'projectNum', ");
        sb.append(" user_job_num as 'userJobNum', ");
        sb.append(" user_count as 'userCount', ");
        sb.append(" social_num as 'socialNum', ");
        sb.append(" party_type as 'partyType' ");
        sb.append(" FROM party_census_info where party_id in (" +
                " select id from ( select DISTINCT used_party_id as id from elevator r where r.branch_office in (select py.id from party py where py.parent_id="+partyId+" and party_type='4') and r.used_party_id is not null " +
                " UNION " +
                " select DISTINCT maintenance_party_id as id from elevator r where r.branch_office in (select py.id from party py where py.parent_id="+partyId+" and party_type='4') and r.maintenance_party_id is not null "+
                " ) a ) "+" and create_date=" + extParty.getCredateDate());

        if (StringUtils.isNotBlank(extParty.getFindType())) {
            sb.append(" and party_type =" + extParty.getFindType() + " ");
        }
        if (StringUtils.isNotBlank(extParty.getPartyName())) {
            sb.append(" and party_name like '%" + extParty.getPartyName() + "%' ");
        }



        return sb.toString();
    }

    /**
     * 分区
     * @param extParty
     * @return
     */
    public String findCensusInfoListAreaCity(ExtParty extParty) {
        StringBuffer sb = new StringBuffer();

        Long partyId = extParty.getId();
        sb.append(" SELECT ");
        sb.append(" party_id as 'id', ");
        sb.append(" party_name as 'partyName', ");
        sb.append(" party_addr as 'partyAddr', ");
        sb.append(" ele_count as 'eleCount', ");
        sb.append(" contract_number as 'contractNumber', ");
        sb.append(" project_num as 'projectNum', ");
        sb.append(" user_job_num as 'userJobNum', ");
        sb.append(" user_count as 'userCount', ");
        sb.append(" social_num as 'socialNum', ");
        sb.append(" party_type as 'partyType' ");
        sb.append(" FROM party_census_info where party_id in (" +
                " select id from ( select DISTINCT used_party_id as id from elevator r where r.branch_office = "+partyId+" and r.used_party_id is not null " +
                " UNION " +
                " select DISTINCT maintenance_party_id as id from elevator r where r.branch_office = "+partyId+" and r.maintenance_party_id is not null "+
                " ) a ) "+" and create_date=" + extParty.getCredateDate());


        if (StringUtils.isNotBlank(extParty.getFindType())) {
            sb.append(" and party_type =" + extParty.getFindType() + " ");
        }
        if (StringUtils.isNotBlank(extParty.getPartyName())) {
            sb.append(" and party_name like '%" + extParty.getPartyName() + "%' ");
        }


        return sb.toString();
    }

    /**
     * 市
     * @param extParty
     * @return
     */
    public String findCensusInfoList(ExtParty extParty) {
        String credateDate = extParty.getCredateDate();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" party_id as 'id', ");
        sb.append(" party_name as 'partyName', ");
        sb.append(" party_addr as 'partyAddr', ");
        sb.append(" ele_count as 'eleCount', ");
        sb.append(" contract_number as 'contractNumber', ");
        sb.append(" project_num as 'projectNum', ");
        sb.append(" user_job_num as 'userJobNum', ");
        sb.append(" user_count as 'userCount', ");
        sb.append(" social_num as 'socialNum', ");
        sb.append(" party_type as 'partyType' ");
        sb.append(" FROM party_census_info where 1=1 ");
        sb.append(" and create_date=#{credateDate} ");

        if (StringUtils.isNotBlank(extParty.getFindType())) {
            sb.append(" and party_type =" + extParty.getFindType() + " ");
        }
        if (StringUtils.isNotBlank(extParty.getPartyName())) {
            sb.append("and party_name like '%" + extParty.getPartyName() + "%' ");
        }

        return sb.toString();
    }

}
